use log::{info, debug, error};
use rusqlite::{params, Result};
use serde::{Deserialize, Serialize};
use tauri::AppHandle;

use crate::db::get_db_connection;

#[derive(Debug, Serialize, Deserialize)]
pub struct Todo {
    pub id: i64,
    pub title: String,
    pub importance: String,
    pub urgency: String,
    pub completed: bool,
    pub start_time: Option<String>,
    pub due_time: Option<String>,
    pub project_id: Option<i64>,
    pub tags: Vec<String>,
    pub markdown_path: Option<String>,
    pub created_at: String,
    pub updated_at: String,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct Project {
    pub id: i64,
    pub name: String,
    pub markdown_path: Option<String>,
    pub created_at: String,
    pub updated_at: String,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct Tag {
    pub id: i64,
    pub name: String,
    pub created_at: String,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct TodoData {
    pub title: String,
    pub importance: String,
    pub urgency: String,
    pub start_time: Option<String>,
    pub due_time: Option<String>,
    pub project_id: Option<i64>,
    pub tags: Vec<String>,
    pub markdown_path: Option<String>,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct PagedResult<T> {
    pub items: Vec<T>,
    pub total: i64,
    pub page: i64,
    pub page_size: i64,
}

#[derive(Debug, Serialize, Deserialize)]
pub enum TimeFrame {
    Today,
    ThisWeek,
    ThisMonth,
    Custom { start: String, end: String },
}

#[derive(Debug, Serialize, Deserialize)]
pub enum SortOrder {
    Asc,
    Desc,
}

#[tauri::command]
pub async fn create_todo(app_handle: AppHandle, todo: TodoData) -> Result<i64, String> {
    info!("Creating new todo: {:?}", todo);
    let mut conn = match get_db_connection(&app_handle) {
        Ok(conn) => {
            debug!("Successfully got database connection");
            conn
        }
        Err(e) => {
            error!("Failed to get database connection: {}", e);
            return Err(e.to_string());
        }
    };
    let tx = conn.transaction().map_err(|e| e.to_string())?;

    tx.execute(
        "INSERT INTO todos (title, importance, urgency, start_time, due_time, project_id, markdown_path)
         VALUES (?, ?, ?, ?, ?, ?, ?)",
        params![
            todo.title,
            todo.importance,
            todo.urgency,
            todo.start_time,
            todo.due_time,
            todo.project_id,
            todo.markdown_path,
        ],
    )
    .map_err(|e| e.to_string())?;
    let todo_id = tx.last_insert_rowid();

    for tag_name in todo.tags {
        let tag_id = match tx.query_row(
            "SELECT id FROM tags WHERE name = ?",
            params![tag_name],
            |row| row.get::<_, i64>(0),
        ) {
            Ok(id) => id,
            Err(_) => {
                tx.execute("INSERT INTO tags (name) VALUES (?)", params![tag_name])
                    .map_err(|e| e.to_string())?;
                tx.last_insert_rowid()
            }
        };

        tx.execute(
            "INSERT INTO todo_tags (todo_id, tag_id) VALUES (?, ?)",
            params![todo_id, tag_id],
        )
        .map_err(|e| e.to_string())?;
    }

    tx.commit().map_err(|e| {
        error!("Failed to commit transaction: {}", e);
        e.to_string()
    })?;
    info!("Successfully created todo with id: {}", todo_id);
    Ok(todo_id)
}

#[tauri::command]
pub async fn get_todo_by_id(app_handle: AppHandle, id: i64) -> Result<Option<Todo>, String> {
    info!("Getting todo by id: {}", id);
    let conn = match get_db_connection(&app_handle) {
        Ok(conn) => {
            debug!("Successfully got database connection");
            conn
        }
        Err(e) => {
            error!("Failed to get database connection: {}", e);
            return Err(e.to_string());
        }
    };
    let mut stmt = conn
        .prepare(
            "SELECT t.*, GROUP_CONCAT(tg.name) as tags 
             FROM todos t 
             LEFT JOIN todo_tags tt ON t.id = tt.todo_id 
             LEFT JOIN tags tg ON tt.tag_id = tg.id 
             WHERE t.id = ? 
             GROUP BY t.id",
        )
        .map_err(|e| e.to_string())?;

    let todo = stmt
        .query_row(params![id], |row| {
            let tags: String = row.get("tags").unwrap_or_default();
            Ok(Todo {
                id: row.get("id")?,
                title: row.get("title")?,
                importance: row.get("importance")?,
                urgency: row.get("urgency")?,
                completed: row.get("completed")?,
                start_time: row.get("start_time")?,
                due_time: row.get("due_time")?,
                project_id: row.get("project_id")?,
                tags: if tags.is_empty() {
                    Vec::new()
                } else {
                    tags.split(',').map(|s| s.to_string()).collect()
                },
                markdown_path: row.get("markdown_path")?,
                created_at: row.get("created_at")?,
                updated_at: row.get("updated_at")?,
            })
        })
        .ok();

    match &todo {
        Some(t) => debug!("Found todo: {:?}", t),
        None => debug!("No todo found with id: {}", id),
    }
    Ok(todo)
}

#[tauri::command]
pub async fn update_todo(
    app_handle: AppHandle,
    id: i64,
    data: TodoData,
) -> Result<(), String> {
    info!("Updating todo id: {} with data: {:?}", id, data);
    let mut conn = match get_db_connection(&app_handle) {
        Ok(conn) => {
            debug!("Successfully got database connection");
            conn
        }
        Err(e) => {
            error!("Failed to get database connection: {}", e);
            return Err(e.to_string());
        }
    };
    let tx = conn.transaction().map_err(|e| e.to_string())?;

    tx.execute(
        "UPDATE todos 
         SET title = ?, importance = ?, urgency = ?, start_time = ?, 
             due_time = ?, project_id = ?, markdown_path = ? 
         WHERE id = ?",
        params![
            data.title,
            data.importance,
            data.urgency,
            data.start_time,
            data.due_time,
            data.project_id,
            data.markdown_path,
            id,
        ],
    )
    .map_err(|e| e.to_string())?;

    tx.execute("DELETE FROM todo_tags WHERE todo_id = ?", params![id])
        .map_err(|e| e.to_string())?;

    for tag_name in data.tags {
        let tag_id = match tx.query_row(
            "SELECT id FROM tags WHERE name = ?",
            params![tag_name],
            |row| row.get::<_, i64>(0),
        ) {
            Ok(id) => id,
            Err(_) => {
                tx.execute("INSERT INTO tags (name) VALUES (?)", params![tag_name])
                    .map_err(|e| e.to_string())?;
                tx.last_insert_rowid()
            }
        };

        tx.execute(
            "INSERT INTO todo_tags (todo_id, tag_id) VALUES (?, ?)",
            params![id, tag_id],
        )
        .map_err(|e| e.to_string())?;
    }

    tx.commit().map_err(|e| {
        error!("Failed to commit transaction: {}", e);
        e.to_string()
    })?;
    info!("Successfully updated todo id: {}", id);
    Ok(())
}

#[tauri::command]
pub async fn delete_todo(app_handle: AppHandle, id: i64) -> Result<(), String> {
    let conn = get_db_connection(&app_handle).map_err(|e| e.to_string())?;
    conn.execute("DELETE FROM todos WHERE id = ?", params![id])
        .map_err(|e| {
            error!("Failed to delete todo id: {} - {}", id, e);
            e.to_string()
        })?;
    info!("Successfully deleted todo id: {}", id);
    Ok(())
}

#[tauri::command]
pub async fn update_todo_completion(
    app_handle: AppHandle,
    id: i64,
    completed: bool,
) -> Result<(), String> {
    info!("Updating todo completion status - id: {}, completed: {}", id, completed);
    let conn = match get_db_connection(&app_handle) {
        Ok(conn) => {
            debug!("Successfully got database connection");
            conn
        }
        Err(e) => {
            error!("Failed to get database connection: {}", e);
            return Err(e.to_string());
        }
    };
    conn.execute(
        "UPDATE todos SET completed = ? WHERE id = ?",
        params![completed, id],
    )
    .map_err(|e| {
        error!("Failed to update todo completion status - id: {} - {}", id, e);
        e.to_string()
    })?;
    info!("Successfully updated todo completion status - id: {}, completed: {}", id, completed);
    Ok(())
}

#[tauri::command]
pub async fn get_todos_by_month(
    app_handle: AppHandle,
    year: i32,
    month: u32,
) -> Result<Vec<Todo>, String> {
    let conn = get_db_connection(&app_handle).map_err(|e| e.to_string())?;
    let mut stmt = conn
        .prepare(
            "SELECT t.*, GROUP_CONCAT(tg.name) as tags 
             FROM todos t 
             LEFT JOIN todo_tags tt ON t.id = tt.todo_id 
             LEFT JOIN tags tg ON tt.tag_id = tg.id 
             WHERE strftime('%Y', t.due_time) = ? AND strftime('%m', t.due_time) = ? 
             GROUP BY t.id 
             ORDER BY t.due_time ASC",
        )
        .map_err(|e| e.to_string())?;

    let todos = stmt
        .query_map(
            params![format!("{:04}", year), format!("{:02}", month)],
            |row| {
                let tags: String = row.get("tags").unwrap_or_default();
                Ok(Todo {
                    id: row.get("id")?,
                    title: row.get("title")?,
                    importance: row.get("importance")?,
                    urgency: row.get("urgency")?,
                    completed: row.get("completed")?,
                    start_time: row.get("start_time")?,
                    due_time: row.get("due_time")?,
                    project_id: row.get("project_id")?,
                    tags: if tags.is_empty() {
                        Vec::new()
                    } else {
                        tags.split(',').map(|s| s.to_string()).collect()
                    },
                    markdown_path: row.get("markdown_path")?,
                    created_at: row.get("created_at")?,
                    updated_at: row.get("updated_at")?,
                })
            },
        )
        .map_err(|e| e.to_string())?
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| e.to_string())?;

    Ok(todos)
}

#[tauri::command]
pub async fn get_todos_by_week(
    app_handle: AppHandle,
    week_start_date: String,
) -> Result<Vec<Todo>, String> {
    let conn = get_db_connection(&app_handle).map_err(|e| e.to_string())?;
    let mut stmt = conn
        .prepare(
            "SELECT t.*, GROUP_CONCAT(tg.name) as tags 
             FROM todos t 
             LEFT JOIN todo_tags tt ON t.id = tt.todo_id 
             LEFT JOIN tags tg ON tt.tag_id = tg.id 
             WHERE date(t.due_time) >= date(?) AND date(t.due_time) < date(?, '+7 days') 
             GROUP BY t.id 
             ORDER BY t.due_time ASC",
        )
        .map_err(|e| e.to_string())?;

    let todos = stmt
        .query_map(params![week_start_date, week_start_date], |row| {
            let tags: String = row.get("tags").unwrap_or_default();
            Ok(Todo {
                id: row.get("id")?,
                title: row.get("title")?,
                importance: row.get("importance")?,
                urgency: row.get("urgency")?,
                completed: row.get("completed")?,
                start_time: row.get("start_time")?,
                due_time: row.get("due_time")?,
                project_id: row.get("project_id")?,
                tags: if tags.is_empty() {
                    Vec::new()
                } else {
                    tags.split(',').map(|s| s.to_string()).collect()
                },
                markdown_path: row.get("markdown_path")?,
                created_at: row.get("created_at")?,
                updated_at: row.get("updated_at")?,
            })
        })
        .map_err(|e| e.to_string())?
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| e.to_string())?;

    Ok(todos)
}

#[tauri::command]
pub async fn get_todos_for_day(
    app_handle: AppHandle,
    date: String,
) -> Result<Vec<Todo>, String> {
    let conn = get_db_connection(&app_handle).map_err(|e| e.to_string())?;
    let mut stmt = conn
        .prepare(
            "SELECT t.*, GROUP_CONCAT(tg.name) as tags 
             FROM todos t 
             LEFT JOIN todo_tags tt ON t.id = tt.todo_id 
             LEFT JOIN tags tg ON tt.tag_id = tg.id 
             WHERE date(t.due_time) = date(?) 
             GROUP BY t.id 
             ORDER BY t.due_time ASC",
        )
        .map_err(|e| e.to_string())?;

    let todos = stmt
        .query_map(params![date], |row| {
            let tags: String = row.get("tags").unwrap_or_default();
            Ok(Todo {
                id: row.get("id")?,
                title: row.get("title")?,
                importance: row.get("importance")?,
                urgency: row.get("urgency")?,
                completed: row.get("completed")?,
                start_time: row.get("start_time")?,
                due_time: row.get("due_time")?,
                project_id: row.get("project_id")?,
                tags: if tags.is_empty() {
                    Vec::new()
                } else {
                    tags.split(',').map(|s| s.to_string()).collect()
                },
                markdown_path: row.get("markdown_path")?,
                created_at: row.get("created_at")?,
                updated_at: row.get("updated_at")?,
            })
        })
        .map_err(|e| e.to_string())?
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| e.to_string())?;

    Ok(todos)
}

#[tauri::command]
pub async fn get_todos_by_tag(
    app_handle: AppHandle,
    tag_name: String,
) -> Result<Vec<Todo>, String> {
    let conn = get_db_connection(&app_handle).map_err(|e| e.to_string())?;
    let mut stmt = conn
        .prepare(
            "SELECT t.*, GROUP_CONCAT(tg.name) as tags 
             FROM todos t 
             JOIN todo_tags tt ON t.id = tt.todo_id 
             JOIN tags tg ON tt.tag_id = tg.id 
             WHERE tg.name = ? 
             GROUP BY t.id 
             ORDER BY t.due_time ASC",
        )
        .map_err(|e| e.to_string())?;

    let todos = stmt
        .query_map(params![tag_name], |row| {
            let tags: String = row.get("tags").unwrap_or_default();
            Ok(Todo {
                id: row.get("id")?,
                title: row.get("title")?,
                importance: row.get("importance")?,
                urgency: row.get("urgency")?,
                completed: row.get("completed")?,
                start_time: row.get("start_time")?,
                due_time: row.get("due_time")?,
                project_id: row.get("project_id")?,
                tags: if tags.is_empty() {
                    Vec::new()
                } else {
                    tags.split(',').map(|s| s.to_string()).collect()
                },
                markdown_path: row.get("markdown_path")?,
                created_at: row.get("created_at")?,
                updated_at: row.get("updated_at")?,
            })
        })
        .map_err(|e| e.to_string())?
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| e.to_string())?;

    Ok(todos)
}

#[tauri::command]
pub async fn get_all_todos_by_project(
    app_handle: AppHandle,
    project_id: i64,
) -> Result<Vec<Todo>, String> {
    let conn = get_db_connection(&app_handle).map_err(|e| e.to_string())?;
    let mut stmt = conn
        .prepare(
            "SELECT t.*, GROUP_CONCAT(tg.name) as tags 
             FROM todos t 
             LEFT JOIN todo_tags tt ON t.id = tt.todo_id 
             LEFT JOIN tags tg ON tt.tag_id = tg.id 
             WHERE t.project_id = ? 
             GROUP BY t.id 
             ORDER BY t.due_time ASC",
        )
        .map_err(|e| e.to_string())?;

    let todos = stmt
        .query_map(params![project_id], |row| {
            let tags: String = row.get("tags").unwrap_or_default();
            Ok(Todo {
                id: row.get("id")?,
                title: row.get("title")?,
                importance: row.get("importance")?,
                urgency: row.get("urgency")?,
                completed: row.get("completed")?,
                start_time: row.get("start_time")?,
                due_time: row.get("due_time")?,
                project_id: row.get("project_id")?,
                tags: if tags.is_empty() {
                    Vec::new()
                } else {
                    tags.split(',').map(|s| s.to_string()).collect()
                },
                markdown_path: row.get("markdown_path")?,
                created_at: row.get("created_at")?,
                updated_at: row.get("updated_at")?,
            })
        })
        .map_err(|e| e.to_string())?
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| e.to_string())?;

    Ok(todos)
}

#[tauri::command]
pub async fn get_recent_todos_by_project(
    app_handle: AppHandle,
    project_id: i64,
) -> Result<Vec<Todo>, String> {
    let conn = get_db_connection(&app_handle).map_err(|e| e.to_string())?;
    let mut stmt = conn
        .prepare(
            "SELECT t.*, GROUP_CONCAT(tg.name) as tags 
             FROM todos t 
             LEFT JOIN todo_tags tt ON t.id = tt.todo_id 
             LEFT JOIN tags tg ON tt.tag_id = tg.id 
             WHERE t.project_id = ? 
             GROUP BY t.id 
             ORDER BY t.due_time ASC 
             LIMIT 5",
        )
        .map_err(|e| e.to_string())?;

    let todos = stmt
        .query_map(params![project_id], |row| {
            let tags: String = row.get("tags").unwrap_or_default();
            Ok(Todo {
                id: row.get("id")?,
                title: row.get("title")?,
                importance: row.get("importance")?,
                urgency: row.get("urgency")?,
                completed: row.get("completed")?,
                start_time: row.get("start_time")?,
                due_time: row.get("due_time")?,
                project_id: row.get("project_id")?,
                tags: if tags.is_empty() {
                    Vec::new()
                } else {
                    tags.split(',').map(|s| s.to_string()).collect()
                },
                markdown_path: row.get("markdown_path")?,
                created_at: row.get("created_at")?,
                updated_at: row.get("updated_at")?,
            })
        })
        .map_err(|e| e.to_string())?
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| e.to_string())?;

    Ok(todos)
}

#[tauri::command]
pub async fn create_project(
    app_handle: AppHandle,
    name: String,
    markdown_path: Option<String>,
) -> Result<i64, String> {
    let conn = get_db_connection(&app_handle).map_err(|e| e.to_string())?;
    conn.execute(
        "INSERT INTO projects (name, markdown_path) VALUES (?, ?)",
        params![name, markdown_path],
    )
    .map_err(|e| e.to_string())?;
    let id = conn.last_insert_rowid();
    Ok(id)
}

#[tauri::command]
pub async fn get_all_projects(app_handle: AppHandle) -> Result<Vec<Project>, String> {
    let conn = get_db_connection(&app_handle).map_err(|e| e.to_string())?;
    let mut stmt = conn
        .prepare("SELECT * FROM projects ORDER BY name ASC")
        .map_err(|e| e.to_string())?;

    let projects = stmt
        .query_map([], |row| {
            Ok(Project {
                id: row.get("id")?,
                name: row.get("name")?,
                markdown_path: row.get("markdown_path")?,
                created_at: row.get("created_at")?,
                updated_at: row.get("updated_at")?,
            })
        })
        .map_err(|e| e.to_string())?
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| e.to_string())?;

    Ok(projects)
}

#[tauri::command]
pub async fn update_project(
    app_handle: AppHandle,
    id: i64,
    name: String,
    markdown_path: Option<String>,
) -> Result<(), String> {
    let conn = get_db_connection(&app_handle).map_err(|e| e.to_string())?;
    conn.execute(
        "UPDATE projects SET name = ?, markdown_path = ? WHERE id = ?",
        params![name, markdown_path, id],
    )
    .map_err(|e| e.to_string())?;
    Ok(())
}

#[tauri::command]
pub async fn get_all_tags(app_handle: AppHandle) -> Result<Vec<Tag>, String> {
    let conn = get_db_connection(&app_handle).map_err(|e| e.to_string())?;
    let mut stmt = conn
        .prepare("SELECT * FROM tags ORDER BY name ASC")
        .map_err(|e| e.to_string())?;

    let tags = stmt
        .query_map([], |row| {
            Ok(Tag {
                id: row.get("id")?,
                name: row.get("name")?,
                created_at: row.get("created_at")?,
            })
        })
        .map_err(|e| e.to_string())?
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| e.to_string())?;

    Ok(tags)
}

#[tauri::command]
pub async fn query_todos_generic(
    app_handle: AppHandle,
    timeframe: TimeFrame,
    sort_by: String,
    order: SortOrder,
    page: i64,
    page_size: i64,
) -> Result<PagedResult<Todo>, String> {
    let conn = get_db_connection(&app_handle).map_err(|e| e.to_string())?;

    let where_clause = match timeframe {
        TimeFrame::Today => "date(t.due_time) = date('now')",
        TimeFrame::ThisWeek => "date(t.due_time) >= date('now', 'weekday 0', '-6 days') AND date(t.due_time) <= date('now')",
        TimeFrame::ThisMonth => "strftime('%Y-%m', t.due_time) = strftime('%Y-%m', 'now')",
        TimeFrame::Custom { start, end } => {
            let clause = format!("date(t.due_time) >= date('{}') AND date(t.due_time) <= date('{}')", start, end);
            Box::leak(clause.into_boxed_str())
        }
    };

    let order_clause = format!(
        "ORDER BY {} {}",
        sort_by,
        match order {
            SortOrder::Asc => "ASC",
            SortOrder::Desc => "DESC",
        }
    );

    let offset = (page - 1) * page_size;

    let mut stmt = conn
        .prepare(&format!(
            "SELECT t.*, GROUP_CONCAT(tg.name) as tags 
             FROM todos t 
             LEFT JOIN todo_tags tt ON t.id = tt.todo_id 
             LEFT JOIN tags tg ON tt.tag_id = tg.id 
             WHERE {} 
             GROUP BY t.id 
             {} 
             LIMIT ? OFFSET ?",
            where_clause, order_clause
        ))
        .map_err(|e| e.to_string())?;

    let todos = stmt
        .query_map(params![page_size, offset], |row| {
            let tags: String = row.get("tags").unwrap_or_default();
            Ok(Todo {
                id: row.get("id")?,
                title: row.get("title")?,
                importance: row.get("importance")?,
                urgency: row.get("urgency")?,
                completed: row.get("completed")?,
                start_time: row.get("start_time")?,
                due_time: row.get("due_time")?,
                project_id: row.get("project_id")?,
                tags: if tags.is_empty() {
                    Vec::new()
                } else {
                    tags.split(',').map(|s| s.to_string()).collect()
                },
                markdown_path: row.get("markdown_path")?,
                created_at: row.get("created_at")?,
                updated_at: row.get("updated_at")?,
            })
        })
        .map_err(|e| e.to_string())?
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| e.to_string())?;

    let total: i64 = conn
        .query_row(
            "SELECT COUNT(DISTINCT t.id) FROM todos t WHERE t.id > 0",
            [],
            |row| row.get(0),
        )
        .map_err(|e| e.to_string())?;

    Ok(PagedResult {
        items: todos,
        total,
        page,
        page_size,
    })
} 